package com.teach.dao.impl;

import com.teach.dto.MedalDto;
import com.teach.dto.MedalSearch;
import com.teach.entity.MedalInfo;
import com.teach.dao.MedalInfoDAO;
import com.teach.util.DBUtil;
import com.teach.util.DBUtil.BaseDAOImpl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * <pre>
 * - 获奖信息数据访问实现类
 * Author : J.L.Zhou
 * E-Mail : 12020042@qq.com
 * Tel : 151 1104 7708
 * Date : 2022-05-04 13:51
 * Version : 1.0
 * Copyright 2021 jlzhou.top Inc. All rights reserved.
 * Warning: this content is only for internal circulation of the company.
 *          It is forbidden to divulge it or use it for other commercial purposes.
 * </pre>
 */
public class MedalInfoDAOImpl extends BaseDAOImpl<MedalInfo> implements MedalInfoDAO {

  @Override
  public List<MedalDto> selectMap(int index, int len) throws SQLException {
    String sql = "SELECT\n" +
        " a.*,\n" +
        " b.sportsman_name,\n" +
        " b.sportsman_uri,\n" +
        " c.country_code,\n" +
        " c.country_uri,\n" +
        " c.country_name,\n" +
        " d.se_name\n" +
        "FROM medal_info a\n" +
        "INNER JOIN sportsman_info b\n" +
        "ON a.sportsman_id=b.sportsman_id\n" +
        "INNER JOIN country_info c\n" +
        "ON b.country_code=c.country_code\n" +
        "INNER JOIN sporting_events d\n" +
        "ON d.se_id = a.se_id order by a.medal_date desc,a.medal_id desc limit ?,?";
    return DBUtil.selectList(sql, MedalDto.class, index, len);
  }

  @Override
  public List<MedalDto> selectSearch(MedalSearch search) throws SQLException {
    List<Object> ps = new ArrayList<>();
    String where = where(search, ps);

    String sql = "SELECT\n" +
        " a.*,\n" +
        " b.sportsman_name,\n" +
        " b.sportsman_uri,\n" +
        " c.country_code,\n" +
        " c.country_uri,\n" +
        " c.country_name,\n" +
        " d.se_name\n" +
        "FROM medal_info a\n" +
        "INNER JOIN sportsman_info b\n" +
        "ON a.sportsman_id=b.sportsman_id\n" +
        "INNER JOIN country_info c\n" +
        "ON b.country_code=c.country_code\n" +
        "INNER JOIN sporting_events d\n" +
        "ON d.se_id = a.se_id " + where + " order by a.medal_date desc,a.medal_num,a.medal_id desc limit ?,?";

    ps.add((search.getCurrent() - 1) * search.getRowMax());
    ps.add(search.getRowMax());
    System.out.println(sql);
    System.out.println(ps);
    return DBUtil.selectList(sql, MedalDto.class, ps.toArray());
  }


  @Override
  public long countSearch(MedalSearch search) throws SQLException {
    List<Object> ps = new ArrayList<>();
    String where = where(search, ps);

    String sql = "SELECT count(*) as c " +
        "FROM medal_info a\n" +
        "INNER JOIN sportsman_info b\n" +
        "ON a.sportsman_id=b.sportsman_id\n" +
        "INNER JOIN country_info c\n" +
        "ON b.country_code=c.country_code\n" +
        "INNER JOIN sporting_events d\n" +
        "ON d.se_id = a.se_id " + where;
    return DBUtil.selectBigDecimal(sql, ps.toArray()).longValue();

  }

  private String where(MedalSearch search, List<Object> list) {
    StringBuilder sb = new StringBuilder();
    if(search.getKey()!=null&&!"".equals(search.getKey())) {
      sb.append(" AND b.sportsman_name LIKE CONCAT('%',?,'%') ");
      list.add(search.getKey());
    }
    if(search.getCountryCode()!=null && !"".equals(search.getCountryCode())){
      sb.append(" and c.country_code=? ");
      list.add(search.getCountryCode());
    }
    if(search.getMedalNum()!=null){
      sb.append(" and a.medal_num=? ");
      list.add(search.getMedalNum());
    }
    if(search.getSeId()!=null){
      sb.append(" and d.se_id=? ");
      list.add(search.getSeId());
    }
    if(search.getSportsmanId()!=null){
      sb.append(" and b.sportsman_id=? ");
      list.add(search.getSportsmanId());
    }
    return sb.toString();
  }
}
